import mysql from "mysql";
/*
    传入对象参数时，不允许缺少属性；
    如果没有值，可传入undefined；
    部分属性必须有合法的值；
*/

export class AdminData {
    id!: string;
    password!: string;
    state!: number;
    group!: number;
    name!: string;
}



export class InterviewDetailsData {
    judge!: string;
    stu_id!: string;
    score!: number;
    evalu!: string | undefined;
    state!: number;
}

export class StuInformData {
    id!: string;
    state!: number;
    group!: number | undefined;
    name!: string;
    class!: string;
    qq!: string | undefined;
    tel!: string | undefined;
    why!: string | undefined;
    hobby!: string | undefined;
    time!: number | undefined;
    firstscore!: number;
    secondscore!: number;
    thirdscore!: number;
    fourthscore!: number;
}

export class StuPassData {
    id!: string;
    password!: string;
}

export class StuScoreData {
    id!: string;
    first_aver_score!: number | undefined;
    second_aver_score!: number | undefined;
    third_aver_score!: number | undefined;
    fourth_aver_score!: number | undefined;
}

export class queryPara {
    group!: Array<number> | undefined;
    status!: number;
    time!: Array<number> | undefined;
}
/*
{
    host:string,  主机
    port:number, 端口
    user:string, 用户
    password:string, 密码
    database:string 数据库名
}
*/
export class DatabasePara {
    host!: string;
    port!: number;
    user!: string;
    password!: string;
    database!: string;
}

export default function createDatabase(req:DatabasePara):Database{
    const db = mysql.createConnection({
        host: req.host,
        port: req.port, 
        user: req.user, 
        password: req.password, 
        database: req.database
    });
    return new Database(db);
}

export class Database {
    db:mysql.Connection;
    constructor(db:mysql.Connection) {
        this.db = db;
    }

    //.then(data,err)的首参数为解析后的数据
    queryBySQL(SQL:string):Promise<any> {
        let _this:this = this;
        console.log(SQL);
        return new Promise((resolve, reject) => {
            _this.db.query(SQL, (err:any, data:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(data)));
                }
            });
        });
        
    }

    queryStuInformByID(id:string):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.db.query(`SELECT * FROM stu_infom_table WHERE id='${id}'`, (err:any, data:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(data)));
                }
            });
        });
    }

    queryStuPassByID(id:string):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.db.query(`SELECT * FROM stu_pass_table WHERE id='${id}'`, (err:any, data:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(data)));
                }
            });
        });
    }

    queryStuScoreByID(id:string):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.db.query(`SELECT * FROM stu_score_table WHERE id='${id}'`, (err:any, data:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(data)));
                }
            });
        });
    }

    queryStuInterviewByID(id:string):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.db.query(`SELECT * FROM interview_details_table WHERE stu_id='${id}'`, (err:any, data:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(data)));
                }
            });
        });
    }

    queryAdminInformByID(id:string):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.db.query(`SELECT * FROM admin_table WHERE id='${id}'`, (err:any, data:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(data)));
                }
            });
        });
    }

    insertToAdmin(data:AdminData):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.db.query(`INSERT INTO admin_table 
            (id, password, state, \`group\`, \`name\`) 
            VALUES ('${data.id}', '${data.password}', ${data.state}, ${data.group}, '${data.name}')`, 
            (err:any, resData:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(resData)));
                }
            });
        });
    }

    insertTostuPass(data:StuPassData):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.db.query(`INSERT INTO stu_pass_table 
            (id, password) 
            VALUES ('${data.id}', '${data.password}')`, 
            (err:any, resData:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(resData)));
                }
            });
        });
    }

    insertTostuScore(data:StuScoreData):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.db.query(`INSERT INTO stu_score_table 
            (id, first_aver_score, second_aver_score, third_aver_score, fourth_aver_score) 
            VALUES ('${data.id}', ${data.first_aver_score!=undefined?data.first_aver_score:-1}, ${data.second_aver_score!=undefined?data.second_aver_score:-1}, ${data.third_aver_score!=undefined?data.third_aver_score:-1}, ${data.fourth_aver_score!=undefined?data.fourth_aver_score:-1})`, 
            (err:any, resData:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(resData)));
                }
            });
        });
    }

    insertTointerviewDetails(data:InterviewDetailsData):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.db.query(`INSERT INTO interview_details_table 
            (judge, stu_id, score, evalu, state) 
            VALUES ('${data.judge}', '${data.stu_id}', ${data.score}, '${data.evalu}', ${data.state})`, 
            (err:any, resData:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(resData)));
                }
            });
        });
    }

    insertTostuInform(data:StuInformData):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {

            _this.db.query(`INSERT INTO stu_infom_table 
            (id, \`name\`, class, qq, tel, why, hobby, \`group\`, time, state, firstscore, secondscore, thirdscore, fourthscore) 
            VALUES ('${data.id}', '${data.name}', '${data.class}', '${data.qq}', '${data.tel}', '${data.why}', '${data.hobby}', ${data.group!=undefined?data.group:-1}, ${data.time!=undefined?data.time:-1}, ${data.state}, ${data.firstscore}, ${data.secondscore}, ${data.thirdscore}, ${data.fourthscore})`, 
            (err:any, resData:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(resData)));
                }
            });
        });
    }

    upadateStuState(id:string, state:number):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.db.query(`UPDATE stu_infom_table
            SET state=${state}
            WHERE id='${id}';`, (err:any, data:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(data)));
                }
            });
        })
    }

    //更新平均数
    updateStuScore(id:string, score:number):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.queryStuInformByID(id).then((data) => {
                let num:number = 0;
                let str:string = "";
                if(data[0].state > 199 && data[0].state < 299) {
                    num = data[0].state % 100;
                    switch(num) {
                        case 1:
                            str = "firstscore";
                            break;
                        case 2:
                            str = "secondscore";
                            break;
                        case 3:
                            str = "thirdscore";
                            break;
                        case 4:
                            str = "fourthscore";
                            break;
                    }
                } else {
                    reject("现在还不能评价");
                }
                _this.db.query(`UPDATE stu_infom_table
                SET ${str}=${score}
                WHERE id='${id}';`, (err, data) => {
                    if(err) {
                        reject(err);
                    } else {
                        resolve(JSON.parse(JSON.stringify(data)));
                    }
                });
            });
        })
        
    }

    upadateAdmState(id:string, state:number):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.db.query(`UPDATE admin_table
            SET state=${state}
            WHERE id='${id}';`, (err:any, data:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(data)));
                }
            });
        })
    }
    
    queryNumByTime(time:number, group:number):Promise<any> {
        let _this:this = this;
        return new Promise((resolve, reject) => {
            _this.db.query(`SELECT * 
            FROM stu_infom_table
            WHERE time='${time}' AND \`group\`=${group};`, (err:any, data:string) => {
                if(err) {
                    reject(err);
                }
                else {
                    resolve(JSON.parse(JSON.stringify(data)).length);
                }
            });
        })
    }

    queryStus(data:queryPara, page:number, pageNum:number):Promise<any> {
        let _this:this = this;
        let SQL:string = "";
        let flag:number = 0;
        let num: number = data.status - 100;
        let str: string = "";
        let statusArr:Array<number> = new Array();
        switch(num) { 
            case 0:
                str = "id";
            case 1:
                str = "firstscore";
                break;
            case 2:
                str = "secondscore";
                break;
            case 3:
                str = "thirdscore";
                break;
            case 4:
                str = "fourthscore";
                break;
        }
        if(data.group || data.status || data.time) {
            SQL = SQL + " WHERE (";
        }
        if(data.group) {
            flag++;
            if(flag > 1) {
                SQL = SQL + ") AND ("
            }
            for(let i = 0; i < data.group.length; i++) {
                if(i >= 1) {
                    SQL = SQL + "OR "
                }
                SQL = SQL + `\`group\`=${data.group[i]} `;
            }
        }
       
        if(data.status) {
            for(let i = 1; i < 5; i++) {
                statusArr.push(data.status % 100 + i * 100);
            }
            flag++;
            if(flag > 1) {
                SQL = SQL + ") AND ("
            }
            for(let i = 0; i < statusArr.length; i++) {
                if(i >= 1) {
                    SQL = SQL + "OR "
                }
                SQL = SQL + `state=${statusArr[i]} `;
            }
        }
        if(data.time && data.time.length && data.time[0]) {
            flag++;
            if(flag > 1) {
                SQL = SQL + ") AND ("
            }
            for(let i = 0; i < data.time.length; i++) {
                if(i >= 1) {
                    SQL = SQL + "OR "
                }
                SQL = SQL + `time=${data.time[i]} `;
            }
            
        }  
        SQL = SQL + ") ";
        
        return new Promise((resolve, rej) => {
            (async () => {
                let res = {
                    sum : 0,
                    resData: []
                };
                res.resData = await new Promise((resolve, reject) => {
                    console.log("SELECT * FROM stu_infom_table" + SQL + `ORDER BY ${str} DESC` + ` LIMIT ${(pageNum * (page-1))===0?"":pageNum * (page-1)} ${pageNum}`)
                    _this.db.query("SELECT * FROM stu_infom_table" + SQL + `ORDER BY ${str} DESC` + ` LIMIT ${(pageNum * (page-1))===0?"":pageNum * (page-1) + ","} ${pageNum}`, (err:any, resData:string) => {
                        if(err) {
                            reject(err);
                        }
                        else {
                            resolve(JSON.parse(JSON.stringify(resData)));
                        }
                    })
                });
                res.sum = await new Promise((resolve, rej) => {
                    _this.db.query("SELECT COUNT(*) FROM stu_infom_table" + SQL, (err:any, resData:string) => {
                        if(err) {
                            rej(err);
                        }
                        else {
                            console.log(JSON.parse(JSON.stringify(resData))[0]["COUNT(*)"]);
                            resolve(JSON.parse(JSON.stringify(resData))[0]["COUNT(*)"]);
                        }
                    });
                });
                resolve(res);
            })();
        })
        
    }
}